package doInterce.impl;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.FileUtil;
import doInterce.DoInterface;
import utils.DBUtil;
import utils.YmlUtil;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @Description: 查找相同的字段
 * @Author: syh
 * @Date: 2023/12/22/11:20
 */
public class DoSameField implements DoInterface {
    @Override
    public void doExecute() {
        File tab1File = new File("C:\\Users\\Yi\\Desktop\\查找相同的数据\\tab1.txt");
        File tab2File = new File("C:\\Users\\Yi\\Desktop\\查找相同的数据\\tab2.txt");

        List<String> tableFieldList1 = FileUtil.readLines(tab1File, "utf-8");
        List<String> tableFieldList2 = FileUtil.readLines(tab2File, "utf-8");

        List<String> strList=new ArrayList<>();
        Map<String, String> tabFieldMap2 = tableFieldList2.stream().map(ele->ele.split(",",-1)).collect(Collectors.toMap(ele -> ele[1], ele -> ele[0],(a,b)->a));
        for(String field:tableFieldList1){
            String[] arr = field.split(",",-1);
            if(tabFieldMap2.containsKey(arr[1])){
                System.out.println(arr[0]+"!="+tabFieldMap2.get(arr[1])+"  "+arr[1]);
                strList.add("tab1."+arr[0]+"!="+"tab2."+tabFieldMap2.get(arr[1]));
            }
        }

//        System.out.println(" ("+ CollectionUtil.join(strList," or ")+" )");

    }

    public static void main(String[] args) throws SQLException {
        String M_M02 = "EUUID,人员主键,\n" +
                "M0201,姓名,\n" +
                "M0202,性别,M_D_C13\n" +
                "M0203,身份证号,\n" +
                "M0204,出生年月,\n" +
                "M0204_1,年龄,\n" +
                "M0205,民族,M_D_CM16\n" +
                "M0206,政治面貌,M_D_CD17\n" +
                "M0207,现工作单位及职务,\n" +
                "M0208,现职务层次,M_D_C32_2019\n" +
                "M0209,上年度职务层次,M_D_C32_2019\n" +
                "M0210,上年度考核结果,M_D_C32\n" +
                "M0211,上年度是否为领导成员,M_D_C01\n" +
                "M0212,最高学历,M_D_C11\n" +
                "M0213,最高学位,M_D_C12\n" +
                "M0214,是否兼任下级领导职务,M_D_C01\n" +
                "M0215,是否具有2年以上基层经验,M_D_C01\n" +
                "M0216,参加工作时间,\n" +
                "M0217,人员类别代码,M_D_M0217\n" +
                "M0218,是否属乡镇人大机关,M_D_C01\n" +
                "M0219,人员管理状态,M_D_M0219\n" +
                "M0220,是否晋升职务,M_D_C01\n" +
                "M0221,晋升后级别,\n" +
                "M0222,晋升时间,\n" +
                "M0223,前一职级任职时间,\n" +
                "M0223_1,前职级年限,\n" +
                "M0224,晋升类别,M_D_M0224\n" +
                "M0225,晋升时学历,M_D_M0225\n" +
                "M0226,晋升时是否中共党员,M_D_C01\n" +
                "M0227,是否破格提拔,M_D_C01\n" +
                "M0228,是否越级提拔,M_D_C01\n" +
                "M0229,是否晋升职级,M_D_C01\n" +
                "M0230,晋升前职级类别,M_D_M0230\n" +
                "M0231,本年底享受职级待遇情况,M_D_M0231\n" +
                "M0232,本年度晋升职级待遇情况,M_D_M0232\n" +
                "M0233,任现职务层次时间,\n" +
                "M0233_1,任现职务层次年限,\n" +
                "M0234,现职务层次补充项,M_D_M0234\n" +
                "M0240,少数民族（内蒙专用）,M_D_MNM0205\n" +
                "M0241,专业（内蒙专用）,M_D_MNM0207\n" +
                "M0242,全日制学历,M_D_C11\n" +
                "M0243,现职级层次,M_D_C31_2019\n" +
                "M0244,任现职级层次时间,\n" +
                "M0244_1,任现职级年限,\n" +
                "M0245,消防专业技术职务,M_D_M0245\n" +
                "M0246,职位类别,M_D_M0246_1\n" +
                "M0247,是否外派挂职,M_D_C01\n" +
                "M0248,是否具有援藏援疆援青工作经历,M_D_C01\n" +
                "M0249,新任综合管理类领导职务调整前职务职级,M_D_M0249\n" +
                "M0251,上年度考核结果,M_D_C32_2021\n" +
                "M0299,备注,\n" +
                "ORDERBY,排序号,\n" +
                "OUUID,单位编码,\n" +
                "RNUM,排序号,\n" +
                "RPT,报告期,\n" +
                "SID,人员SID,\n" +
                "UNID,归属单位编码,\n";

        String M_M05="EUUID,人员主键,\n" +
                "ID,,\n" +
                "M0501,姓名,\n" +
                "M0502,性别,M_D_C13\n" +
                "M0503,身份证号,\n" +
                "M0504,民族,M_D_CM16\n" +
                "M0505,出生年月,\n" +
                "M0505_1,年龄,\n" +
                "M0506,政治面貌,M_D_CD17\n" +
                "M0507,最高学历,M_D_C11\n" +
                "M0508,最高学位,M_D_C12\n" +
                "M0509,现工作单位及职务,\n" +
                "M0510,原工作单位及职务,\n" +
                "M0511,增减情况,M_D_M0511\n" +
                "M0512,增加人员类别,M_D_M0512_2019\n" +
                "M0513,录用人员是否具有2年以上基层工作经历,M_D_C01\n" +
                "M0514,减少人员类别,M_D_M0514_2018\n" +
                "M0515,减少人员职务职级层次,M_D_C34\n" +
                "M0516,减少人员上年度是否为领导成员,M_D_C01\n" +
                "M0517,减少人员上年底职务层次,M_D_C32_2019\n" +
                "M0518,减少人员上年度考核结果,M_D_C32\n" +
                "M0519,录用人员是否留学回国人员,M_D_C01\n" +
                "M0520,录用时间,\n" +
                "M0521,调任后职务职级层次,M_D_C34\n" +
                "M0522,调任后职务层次补充项,M_D_M0234\n" +
                "M0523,退休人员职务层次补充项,M_D_M0234\n" +
                "M0524,全日制学历,M_D_C11\n" +
                "M0525,录用人员是否为选调生,M_D_C01\n" +
                "M0599,备注,\n" +
                "ORDERBY,排序号,\n" +
                "OUUID,单位编码,\n" +
                "RNUM,排序号,\n" +
                "RPT,报告期,\n" +
                "SID,人员,\n" +
                "UNID,归属单位编码,\n";

        String M_M07="EUUID,人员主键,\n" +
                "M0701,姓名,\n" +
                "M0702,身份证号,\n" +
                "M0703,性别,M_D_C13\n" +
                "M0704,出生年月,\n" +
                "M0704_1,年龄,\n" +
                "M0705,民族,M_D_CM16\n" +
                "M0706,政治面貌,M_D_CD17\n" +
                "M0707,晋升时最高学历,M_D_C11\n" +
                "M0708,晋升后职务职级层次,M_D_C34\n" +
                "M0709,晋升时间,\n" +
                "M0709_1,晋升时间计算,\n" +
                "M0710,任前一职务职级层次时间,\n" +
                "M0710_1,任前一职务职级年限,\n" +
                "M0711,晋升类别,M_D_M0224\n" +
                "M0712,是否破格提拔,M_D_C01\n" +
                "M0713,是否越级提拔,M_D_C01\n" +
                "M0714,晋升后职务层次补充项,M_D_M0234\n" +
                "M0715,晋升时全日制学历,M_D_C11\n" +
                "M0716,是否从领导职务晋升,M_D_C01\n" +
                "M0717,是否继续兼任领导职务,M_D_C01\n" +
                "M0721,最高学位,M_D_C12\n" +
                "M0722,现工作单位及职务,\n" +
                "M0798,晋升前职务职级层次,M_D_C34\n" +
                "M0799,备注,\n" +
                "ORDERBY,排序号,\n" +
                "OUUID,单位编码,\n" +
                "RNUM,排序号,\n" +
                "RPT,报告期,\n" +
                "SID,人员SID,\n" +
                "UNID,归属单位编码,\n";

        String M_M03="EUUID,人员主键,\n" +
                "M0301,姓名,\n" +
                "M0302,培训内容(类型),M_D_M0302\n" +
                "M0303,培训形式,M_D_M0303\n" +
                "M0304,培训渠道,M_D_M0304_2018\n" +
                "M0305,培训时长(天),\n" +
                "M0306,是否出国境培训,M_D_C01\n" +
                "M0307,有关省区市参加对口支援培训,M_D_C01\n" +
                "M0308,学时,\n" +
                "M0309,身份证号,\n" +
                "M0310,现职务职级层次,M_D_C34\n" +
                "M0311,现职务层次补充项,M_D_M0234\n" +
                "M0399,备注,\n" +
                "ORDERBY,排序号,\n" +
                "OUUID,单位编码,\n" +
                "RNUM,排序号,\n" +
                "RPT,报告期,\n" +
                "SID,人员,\n" +
                "UNID,归属单位编码,\n";

        String M_M04="EUUID,人员主键,\n" +
                "M0401,姓名,\n" +
                "M0402,性别,M_D_C13\n" +
                "M0403,身份证号,\n" +
                "M0404,民族,M_D_CM16\n" +
                "M0405,现工作单位及职务,\n" +
                "M0406,交流前职务职级层次,M_D_C34\n" +
                "M0407,是否任现职满10年交流,M_D_C01\n" +
                "M0408,是否回避交流,M_D_C01\n" +
                "M0409,交流形式,M_D_M0409_1\n" +
                "M0410,交流去向,M_D_M0410\n" +
                "M0411,出生年月,\n" +
                "M0411_1,年龄,\n" +
                "M0412,政治面貌,M_D_CD17\n" +
                "M0413,最高学历,M_D_C11\n" +
                "M0414,交流时间,\n" +
                "M0415,交流前职务层次补充项,M_D_M0234\n" +
                "M0416,全日制学历,M_D_C11\n" +
                "M0417,最高学位,M_D_C12\n" +
                "M0499,备注,\n" +
                "ORDERBY,排序号,\n" +
                "OUUID,单位编码,\n" +
                "RNUM,排序号,\n" +
                "RPT,报告期,\n" +
                "SID,人员,\n" +
                "UNID,归属单位编码,\n";

        String M_M06="EUUID,人员主键,\n" +
                "M0601,姓名,\n" +
                "M0602,身份证号,\n" +
                "M0603,时任职务职级层次,M_D_C34\n" +
                "M0604,奖励项目,M_D_M0604_2022\n" +
                "M0605,处分,M_D_M0605\n" +
                "M0606,免职、降职、辞去公职、辞退、辞去领导职务情况,M_D_M0606\n" +
                "M0607,申诉情况,M_D_M0607\n" +
                "M0608,申诉处理决定,M_D_M0608\n" +
                "M0609,是否提出申诉,M_D_C01\n" +
                "M0610,是否受理申诉,M_D_C01\n" +
                "M0611,申诉提出再申诉,M_D_C01\n" +
                "M0611_1,任现职务职级年限,\n" +
                "M0612,是否受理再申诉,M_D_C01\n" +
                "M0613,再申诉处理决定,M_D_M0608\n" +
                "M0614,时任职务层次补充项,M_D_M0234\n" +
                "M0699,备注,\n" +
                "ORDERBY,排序号,\n" +
                "OUUID,单位编码,\n" +
                "RNUM,排序号,\n" +
                "RPT,报告期,\n" +
                "SID,人员,\n" +
                "UNID,归属单位编码,\n";

        String verifySql = "40286a4b8ca5cc60018ca5ecfc930011|【在职情况】表和【变化情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0201,M0202,M0203,M0204,M0204_1,M0205,M0206,M0207,M0208,M0209,M0210,M0211,M0212,M0213,M0214,M0215,M0216,M0217,M0218,M0219,M0220,M0221,M0222,M0223,M0223_1,M0224,M0225,M0226,M0227,M0228,M0229,M0230,M0231,M0232,M0233,M0233_1,M0234,M0240,M0241,M0242,M0243,M0244,M0244_1,M0245,M0246,M0247,M0248,M0249,M0251,M0299,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M02_temp where RPT='2023')M_M02_temp  inner join (select EUUID,ID,M0501,M0502,M0503,M0504,M0505,M0505_1,M0506,M0507,M0508,M0509,M0510,M0511,M0512,M0513,M0514,M0515,M0516,M0517,M0518,M0519,M0520,M0521,M0522,M0523,M0524,M0525,M0599,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M05_temp where RPT='2023')M_M05_temp  on M0203=M0503 and A_UNID=B_UNID where (M0201!=M0501 or M0202!=M0502 or M0204!=M0505 or M0205!=M0504 or M0206!=M0506 or M0207!=M0509 or M0212!=M0507 or M0213!=M0508 or M0242!=M0524 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【在职情况】表和【职务职级晋升】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0201,M0202,M0203,M0204,M0204_1,M0205,M0206,M0207,M0208,M0209,M0210,M0211,M0212,M0213,M0214,M0215,M0216,M0217,M0218,M0219,M0220,M0221,M0222,M0223,M0223_1,M0224,M0225,M0226,M0227,M0228,M0229,M0230,M0231,M0232,M0233,M0233_1,M0234,M0240,M0241,M0242,M0243,M0244,M0244_1,M0245,M0246,M0247,M0248,M0249,M0251,M0299,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M02_temp where RPT='2023')M_M02_temp  inner join (select EUUID,M0701,M0702,M0703,M0704,M0704_1,M0705,M0706,M0707,M0708,M0709,M0709_1,M0710,M0710_1,M0711,M0712,M0713,M0714,M0715,M0716,M0717,M0721,M0722,M0798,M0799,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M07_temp where RPT='2023')M_M07_temp  on M0203=M0702 and A_UNID=B_UNID where  (M0201!=M0701 or M0202!=M0703 or M0204!=M0704 or M0205!=M0705 or M0206!=M0706 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【在职情况】表和【培训情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0201,M0202,M0203,M0204,M0204_1,M0205,M0206,M0207,M0208,M0209,M0210,M0211,M0212,M0213,M0214,M0215,M0216,M0217,M0218,M0219,M0220,M0221,M0222,M0223,M0223_1,M0224,M0225,M0226,M0227,M0228,M0229,M0230,M0231,M0232,M0233,M0233_1,M0234,M0240,M0241,M0242,M0243,M0244,M0244_1,M0245,M0246,M0247,M0248,M0249,M0251,M0299,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M02_temp where RPT='2023')M_M02_temp  inner join (select EUUID,M0301,M0302,M0303,M0304,M0305,M0306,M0307,M0308,M0309,M0310,M0311,M0399,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M03_temp where RPT='2023')M_M03_temp  on M0203=M0309 and A_UNID=B_UNID where  (M0201!=M0301 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【在职情况】表和【交流情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0201,M0202,M0203,M0204,M0204_1,M0205,M0206,M0207,M0208,M0209,M0210,M0211,M0212,M0213,M0214,M0215,M0216,M0217,M0218,M0219,M0220,M0221,M0222,M0223,M0223_1,M0224,M0225,M0226,M0227,M0228,M0229,M0230,M0231,M0232,M0233,M0233_1,M0234,M0240,M0241,M0242,M0243,M0244,M0244_1,M0245,M0246,M0247,M0248,M0249,M0251,M0299,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M02_temp where RPT='2023')M_M02_temp  inner join (select EUUID,M0401,M0402,M0403,M0404,M0405,M0406,M0407,M0408,M0409,M0410,M0411,M0411_1,M0412,M0413,M0414,M0415,M0416,M0417,M0499,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M04_temp where RPT='2023')M_M04_temp  on M0203=M0403 and A_UNID=B_UNID where M0409='2' and  (M0201!=M0401 or M0202!=M0402 or M0204!=M0411 or M0205!=M0404 or M0206!=M0412 or M0207!=M0405 or M0212!=M0413 or M0213!=M0417 or M0242!=M0416 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【在职情况】表和【奖惩情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0201,M0202,M0203,M0204,M0204_1,M0205,M0206,M0207,M0208,M0209,M0210,M0211,M0212,M0213,M0214,M0215,M0216,M0217,M0218,M0219,M0220,M0221,M0222,M0223,M0223_1,M0224,M0225,M0226,M0227,M0228,M0229,M0230,M0231,M0232,M0233,M0233_1,M0234,M0240,M0241,M0242,M0243,M0244,M0244_1,M0245,M0246,M0247,M0248,M0249,M0251,M0299,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M02_temp where RPT='2023')M_M02_temp  inner join (select EUUID,M0601,M0602,M0603,M0604,M0605,M0606,M0607,M0608,M0609,M0610,M0611,M0611_1,M0612,M0613,M0614,M0699,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M06_temp where RPT='2023')M_M06_temp  on M0203=M0602 and A_UNID=B_UNID where  (M0201!=M0601 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【变化情况】表和【职务职级晋升】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,ID,M0501,M0502,M0503,M0504,M0505,M0505_1,M0506,M0507,M0508,M0509,M0510,M0511,M0512,M0513,M0514,M0515,M0516,M0517,M0518,M0519,M0520,M0521,M0522,M0523,M0524,M0525,M0599,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M05_temp where RPT='2023')M_M05_temp inner join (select EUUID,M0701,M0702,M0703,M0704,M0704_1,M0705,M0706,M0707,M0708,M0709,M0709_1,M0710,M0710_1,M0711,M0712,M0713,M0714,M0715,M0716,M0717,M0721,M0722,M0798,M0799,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M07_temp where RPT='2023')M_M07_temp on M0503=M0702 and A_UNID=B_UNID where (M0501!=M0701 or M0502!=M0703 or M0504!=M0705 or M0505!=M0704 or M0506!=M0706 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【变化情况】表和【培训情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,ID,M0501,M0502,M0503,M0504,M0505,M0505_1,M0506,M0507,M0508,M0509,M0510,M0511,M0512,M0513,M0514,M0515,M0516,M0517,M0518,M0519,M0520,M0521,M0522,M0523,M0524,M0525,M0599,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M05_temp where RPT='2023')M_M05_temp inner join (select EUUID,M0301,M0302,M0303,M0304,M0305,M0306,M0307,M0308,M0309,M0310,M0311,M0399,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M03_temp where RPT='2023')M_M03_temp on M0503=M0309 and A_UNID=B_UNID where (M0501!=M0301)\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【变化情况】表和【交流情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,ID,M0501,M0502,M0503,M0504,M0505,M0505_1,M0506,M0507,M0508,M0509,M0510,M0511,M0512,M0513,M0514,M0515,M0516,M0517,M0518,M0519,M0520,M0521,M0522,M0523,M0524,M0525,M0599,ORDERBY,OUUID,RNUM,SID,UNID,UNID AS A_UNID from M_M05_temp where RPT='2023')M_M05_temp inner join (select EUUID,M0401,M0402,M0403,M0404,M0405,M0406,M0407,M0408,M0409,M0410,M0411,M0411_1,M0412,M0413,M0414,M0415,M0416,M0417,M0499,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M04_temp where RPT='2023')M_M04_temp on M0503=M0403 and A_UNID=B_UNID where M0409='2' and  (M0501!=M0401 or M0502!=M0402 or M0504!=M0404 or M0505!=M0411 or M0506!=M0412 or M0507!=M0413 or M0508!=M0417 or M0509!=M0405 or M0524!=M0416 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【变化情况】表和【奖惩情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,ID,M0501,M0502,M0503,M0504,M0505,M0505_1,M0506,M0507,M0508,M0509,M0510,M0511,M0512,M0513,M0514,M0515,M0516,M0517,M0518,M0519,M0520,M0521,M0522,M0523,M0524,M0525,M0599,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M05_temp where RPT='2023')M_M05_temp inner join (select EUUID,M0601,M0602,M0603,M0604,M0605,M0606,M0607,M0608,M0609,M0610,M0611,M0611_1,M0612,M0613,M0614,M0699,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M06_temp where RPT='2023')M_M06_temp on M0503=M0602 and A_UNID=B_UNID where  (M0501!=M0601 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【职务职级晋升】表和【培训情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0701,M0702,M0703,M0704,M0704_1,M0705,M0706,M0707,M0708,M0709,M0709_1,M0710,M0710_1,M0711,M0712,M0713,M0714,M0715,M0716,M0717,M0721,M0722,M0798,M0799,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M07_temp where RPT='2023')M_M07_temp inner join (select EUUID,M0301,M0302,M0303,M0304,M0305,M0306,M0307,M0308,M0309,M0310,M0311,M0399,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M03_temp where RPT='2023')M_M03_temp on M0702=M0309 and A_UNID=B_UNID where  (M0701!=M0301 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【职务职级晋升】表和【交流情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0701,M0702,M0703,M0704,M0704_1,M0705,M0706,M0707,M0708,M0709,M0709_1,M0710,M0710_1,M0711,M0712,M0713,M0714,M0715,M0716,M0717,M0721,M0722,M0798,M0799,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M07_temp where RPT='2023')M_M07_temp inner join (select EUUID,M0401,M0402,M0403,M0404,M0405,M0406,M0407,M0408,M0409,M0410,M0411,M0411_1,M0412,M0413,M0414,M0415,M0416,M0417,M0499,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M04_temp where RPT='2023')M_M04_temp on M0702=M0403 and A_UNID=B_UNID where  M0409='2' and  (M0701!=M0401 or M0703!=M0402 or M0704!=M0411 or M0705!=M0404 or M0706!=M0412 or M0721!=M0417 or M0722!=M0405 )\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【职务职级晋升】表和【奖惩情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0701,M0702,M0703,M0704,M0704_1,M0705,M0706,M0707,M0708,M0709,M0709_1,M0710,M0710_1,M0711,M0712,M0713,M0714,M0715,M0716,M0717,M0721,M0722,M0798,M0799,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M07_temp where RPT='2023')M_M07_temp inner join (select EUUID,M0601,M0602,M0603,M0604,M0605,M0606,M0607,M0608,M0609,M0610,M0611,M0611_1,M0612,M0613,M0614,M0699,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M06_temp where RPT='2023')M_M06_temp on M0702=M0602 and A_UNID=B_UNID where (M0701!=M0601)\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【培训情况】表和【交流情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0301,M0302,M0303,M0304,M0305,M0306,M0307,M0308,M0309,M0310,M0311,M0399,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M03_temp where RPT='2023')M_M03_temp inner join (select EUUID,M0401,M0402,M0403,M0404,M0405,M0406,M0407,M0408,M0409,M0410,M0411,M0411_1,M0412,M0413,M0414,M0415,M0416,M0417,M0499,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M04_temp where RPT='2023')M_M04_temp on M0309=M0403 and A_UNID=B_UNID where  M0409='2' and (M0301!=M0401)\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【培训情况】表和【奖励情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0301,M0302,M0303,M0304,M0305,M0306,M0307,M0308,M0309,M0310,M0311,M0399,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M03_temp where RPT='2023')M_M03_temp inner join (select EUUID,M0601,M0602,M0603,M0604,M0605,M0606,M0607,M0608,M0609,M0610,M0611,M0611_1,M0612,M0613,M0614,M0699,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M06_temp where RPT='2023')M_M06_temp on M0309=M0602 and B_UNID=A_UNID where  (M0301!=M0601)\n" +
                "40286a4b8ca5cc60018ca5ecfc930011|【交流情况】表和【奖惩情况】表的人员基础数据不相同，请核实|select count(1) from (select EUUID,M0401,M0402,M0403,M0404,M0405,M0406,M0407,M0408,M0409,M0410,M0411,M0411_1,M0412,M0413,M0414,M0415,M0416,M0417,M0499,ORDERBY,OUUID,RNUM,SID,UNID,UNID as A_UNID from M_M04_temp where RPT='2023')M_M04_temp inner join (select EUUID,M0601,M0602,M0603,M0604,M0605,M0606,M0607,M0608,M0609,M0610,M0611,M0611_1,M0612,M0613,M0614,M0699,ORDERBY,OUUID,RNUM,SID,UNID,UNID as B_UNID from M_M06_temp where RPT='2023')M_M06_temp on M0403=M0602 and A_UNID=B_UNID where  M0409='2' and (M0401!=M0601)\n" ;


        Map<String,String> sourceDataMap = new HashMap<>();
        sourceDataMap.put("M_M02",M_M02);
        sourceDataMap.put("M_M05",M_M05);
        sourceDataMap.put("M_M07",M_M07);
        sourceDataMap.put("M_M03",M_M03);
        sourceDataMap.put("M_M04",M_M04);
        sourceDataMap.put("M_M06",M_M06);


        Map<String,String> fieldMap = new HashMap<>();

        for(String key:sourceDataMap.keySet()){
            String str = sourceDataMap.get(key);
            String[] arr = str.split("\n");
            for(String s:arr){
                String[] arr1 = s.split(",",-1);
                fieldMap.put(arr1[0],arr1[1]);
//                fieldList.add(arr1[0]);
            }

//            fieldList.remove("RPT");
//            fieldMap.put(key,CollectionUtil.join(fieldList,","));
        }


        List<SqlInfo> sqlList = new ArrayList<>();
        String[] sqlArr = verifySql.split("\n");
        int num = 1;
        int ttt = 1;
        for(String sqlStr : sqlArr){
            String[] arr = sqlStr.split("[|]");
            String desc = arr[1]; //描述
            String sql = arr[2]; //sql
//            System.out.println("第"+ttt+"条sql:"+sql);
            ttt++;
            int index = arr[2].lastIndexOf("where");
            String str1 = arr[2].substring(0,index);
            String str2 = arr[2].substring(index+5);
            String majorSql = str1; //where前面的sql
            String whereSql = str2.replaceAll("M0409='2'","").replaceAll("and","").replaceAll("[(]","").replaceAll("[)]","").trim(); //where后面的sql
            //字段数组
            String[] fieldArr = whereSql.split("or",-1);
            for (int i = 0; i < fieldArr.length; i++) {
                fieldArr[i] = fieldArr[i] == null ? null: fieldArr[i].trim();
            }
//            M_M05_temp
            Pattern compile = Pattern.compile("(._M.._temp)");
            Matcher matcher = compile.matcher(sql);
            matcher.find();
            String table1 = matcher.group();
            matcher.find();
            matcher.find();
            String table2 = matcher.group();

            for (int i = 0; i < fieldArr.length; i++) {

                //平凑出来sql
                String fieldStr = fieldArr[i];
                String resultVerifySql = majorSql+" where ";
                if("M_M04_temp".equalsIgnoreCase(table1) || "M_M04_temp".equalsIgnoreCase(table2)){
                    resultVerifySql = resultVerifySql+" M0409='2' and ";
                }
                resultVerifySql = resultVerifySql+fieldStr;

                //获取字段描述
                String[] str = fieldStr.split("!=");
                String newDesc = desc.replaceAll("人员基础数据",fieldMap.get(str[0])).replaceAll("基础数据",fieldMap.get(str[0]));
                SqlInfo sqlInfo = new SqlInfo(getId(num), newDesc, table1, str[0], resultVerifySql);
                sqlList.add(sqlInfo);
                num++;
            }


        }

        for (SqlInfo sqlInfo : sqlList) {
            System.out.println(sqlInfo.toString());
        }

        Map<String, Object> ymlMap = YmlUtil.getYmlMap();
        String url = Convert.toStr(ymlMap.get("url"));
        String userName = Convert.toStr(ymlMap.get("userName"));
        String password = Convert.toStr(ymlMap.get("password"));
        Connection connection = DBUtil.getConnection(url, userName, password);
        String insertSql = "INSERT INTO hy_qggwy.tj_verify_rule (VSC001, VRU001, VRU002, VRU003, VRU004, VRU005, VRU006, VRU007, VRU008, VRU009, VRU010, VRU011) VALUES ('40286a4b8ca5cc60018ca5ecfc930011', ?, ?, '3', ?, ?, ?, '1', ?,  ?, '-', '-')";
        PreparedStatement statement = connection.prepareStatement(insertSql);

//        id 描述 表名 字段名 表名_temp 描述 sql
        for (SqlInfo sqlInfo : sqlList) {
            statement.setString(1,sqlInfo.getId());
            statement.setString(2,sqlInfo.getDesc());
            statement.setString(3,sqlInfo.getTable().replaceAll("_temp",""));
            statement.setString(4,sqlInfo.getField());
            statement.setString(5,sqlInfo.getTable());
            statement.setString(6,sqlInfo.getDesc());
            statement.setString(7,sqlInfo.getSql());

            statement.executeUpdate();
        }


    }

    public static String getId(int num){
        String id="40286a4b8ca5cc60018ca5ecfc9600";
        return num < 10? id+num : id+"0"+num;
    }


    static class SqlInfo{
        String id;
        String desc;
        String table;
        String field;
        String sql;

        public SqlInfo(String id, String desc, String table, String field, String sql) {
            this.id = id;
            this.desc = desc;
            this.table = table;
            this.field = field;
            this.sql = sql;
        }

        public String getId() {
            return id;
        }

        public void setId(String id) {
            this.id = id;
        }

        public String getDesc() {
            return desc;
        }

        public void setDesc(String desc) {
            this.desc = desc;
        }

        public String getTable() {
            return table;
        }

        public void setTable(String table) {
            this.table = table;
        }

        public String getField() {
            return field;
        }

        public void setField(String field) {
            this.field = field;
        }

        public String getSql() {
            return sql;
        }

        public void setSql(String sql) {
            this.sql = sql;
        }

        @Override
        public String toString() {
            return "SqlInfo{" +
                    "id='" + id + '\'' +
                    ", desc='" + desc + '\'' +
                    ", table='" + table + '\'' +
                    ", field='" + field + '\'' +
                    ", sql='" + sql + '\'' +
                    '}';
        }
    }
}
